# 上传本地rule_flatten表到云端
import json
import pandas as pd
import os
import sys

# 添加父目录到路径，以便导入config和common模块
sys.path.append(os.path.abspath(os.path.join(os.path.dirname(__file__), '..')))

from config import create_db_engine, load_env_db
from common.rule_flatten_handle import flatten_json, unflatten_json

# 创建数据库引擎（本地）
from sqlalchemy import create_engine, text
engine = create_db_engine()

# 创建数据库引擎（云端）
DB_USER_CLOUD, DB_PASSWORD_CLOUD, DB_HOST_CLOUD, DB_PORT_CLOUD, _, DB_DATABASE_CLOUD = load_env_db()
DATABASE_URL_CLOUD = f"mysql+pymysql://{DB_USER_CLOUD}:{DB_PASSWORD_CLOUD}@{DB_HOST_CLOUD}:{DB_PORT_CLOUD}/{DB_DATABASE_CLOUD}"
engine_cloud = create_engine(DATABASE_URL_CLOUD, echo=False, pool_recycle=3600)

def upload_rules():
    # 从本地数据库中读取 rule_flatten 表的数据
    print("Fetching rule_flatten table from local database...")
    with engine.connect() as conn:
        sql = text("SELECT * FROM rule_flatten")
        result = conn.execute(sql)
        rows = result.fetchall()
        columns = result.keys()
        df = pd.DataFrame(rows, columns=columns)
    
    print(f"Found {len(df)} records in local rule_flatten table")
    
    # 清空云端数据库中的 rule_flatten 表
    print("Truncating cloud rule_flatten table...")
    with engine_cloud.connect() as conn_cloud:
        conn_cloud.execute(text("TRUNCATE TABLE rule_flatten"))
        conn_cloud.commit()
    
    # 将数据写入云端数据库中的 rule_flatten 表
    print("Uploading data to cloud rule_flatten table...")
    with engine_cloud.connect() as conn_cloud:
        df.to_sql('rule_flatten', con=conn_cloud, if_exists='append', index=False)
        conn_cloud.commit()
    
    print(f"Rule flatten table has been uploaded to cloud. {len(df)} records uploaded.")
    return df

if __name__ == "__main__":
    upload_rules()